#!/bin/bash

APP=edu

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;then
    do_date=$2
else 
    do_date=`date -d "-1 day" +%F`
fi

dim_user_zip="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table ${APP}.dim_user_zip partition(dt)
select
    id,
    login_name,
    nick_name,
    real_name,
    phone_num,
    email,
    head_img,
    user_level,
    birthday,
    gender,
    create_time,
    operate_time,
    status,
    start_date,
    if(rn=2,date_sub('$do_date',1),end_date) end_date,
    if(rn=1,'9999-12-31',date_sub('$do_date4',1)) dt
from
(
    select
        id,
        login_name,
        nick_name,
        real_name,
        phone_num,
        email,
        head_img,
        user_level,
        birthday,
        gender,
        create_time,
        operate_time,
        status,
        start_date,
        end_date,
        row_number() over(partition by id order by start_date desc) rn
    from
    (
        select
            id,
            login_name,
            nick_name,
            real_name,
            phone_num,
            email,
            head_img,
            user_level,
            birthday,
            gender,
            create_time,
            operate_time,
            status,
            start_date,
            end_date
        from ${APP}.dim_user_zip
        where dt='9999-12-31'
        union
        select
                        id,
                login_name,
                nick_name,
                md5(real_name),
                md5(if(phone_num regexp '^(13[0-9]|14[01456879]|15[0-35-9]|16[2567]|17[0-8]|18[0-9]|19[0-35-9])\\d{8}$',
                    phone_num,null)),
                md5(if(email regexp '^[a-zA-Z0-9_-]+@[a-zA-Z0-9_-]+(\\.[a-zA-Z0-9_-]+)+$',email,null)),
                head_img,
                user_level,
                birthday,
                gender,
                create_time,
                operate_time,
                status,
            '$do_date' start_date,
            '9999-12-31' end_date
        from
        (
            select
                data.id,
                data.login_name,
                data.nick_name,
                data.real_name,
                data.phone_num,
                data.email,
                data.head_img,
                data.user_level,
                data.birthday,
                data.gender,
                data.create_time,
                data.operate_time,
                data.status,
                row_number() over (partition by data.id order by ts desc) rn
            from ${APP}.ods_user_info_inc
            where dt='$do_date'
        )t1
        where rn=1
    )t2
)t3;
"

dim_course_full="
with
course as 
(
select
id,
course_name,
course_slogan,
course_cover_url,
subject_id,
teacher,
publisher_id,
chapter_num,
origin_price,
reduce_amount,
actual_price,
course_introduce
from ${APP}.ods_course_info_full
where dt = '$do_date'
),
subject as 
(
select
id,
subject_name,
category_id
from ${APP}.ods_base_subject_info_full 
where dt = '$do_date'
),
category as 
(
select
id,
category_name,
create_time,
update_time,
deleted
from ${APP}.ods_base_category_info_full 
where dt = '$do_date'
)
insert overwrite table ${APP}.dim_course_full partition(dt='$do_date')
select
course.id,
course.course_name,
course.course_slogan,
course.course_cover_url,
course.subject_id,
subject.subject_name,
subject.category_id,
category.category_name,
course.teacher,
course.publisher_id,
course.chapter_num,
course.origin_price,
course.reduce_amount,
course.actual_price,
course.course_introduce,
category.create_time,
category.update_time,
category.deleted
from course
left join subject on course.subject_id = subject.id
left join category on subject.category_id = category.id; 
"

dim_province_full="
insert overwrite table ${APP}.dim_province_full partition(dt='$do_date')
select 
    id,
    name,
    region_id,
    area_code,
    iso_code,
    iso_3166_2
from ${APP}.ods_base_province_full
where dt = '$do_date';
"

dim_test_paper_full="
insert overwrite table ${APP}.dim_test_paper_full partition(dt='$do_date')
select
id,
paper_title,
course_id,
create_time,
update_time,
publisher_id,
deleted
from ${APP}.ods_test_paper_full
where dt = '$do_date';
"

dim_chapter_full="
insert overwrite table dim_chapter_full partition(dt='$do_date')
select 
id,
chapter_name,
course_id,
video_id,
video_name,
during_sec,
publisher_id,
is_free,
create_time,
update_time,
deleted
from ${APP}.dim_chapter_full 
where dt=date_sub('$do_date',1)
union
(
select
t1.id,
t1.chapter_name,
t1.course_id,
t1.video_id,
t2.video_name,
t2.during_sec,
t2.publisher_id,
t1.is_free,
t2.create_time,
t2.update_time,
t2.deleted
from
(
    (select
id,
chapter_name,
course_id,
video_id,
publisher_id,
is_free,
create_time,
update_time,
deleted      
from ${APP}.ods_chapter_info_full 
where dt = '$do_date') t1 
left join 
(
     select
data.id,
data.video_name,
data.during_sec,
data.video_status,
data.video_size,
data.video_url,
data.video_source_id,
data.version_id,
data.chapter_id,
data.course_id,
data.publisher_id,
data.create_time,
data.update_time,
data.deleted
     from  ${APP}.ods_video_info_inc
     where  dt = '$do_date' 
     and type = 'bootstrap-insert' 
) t2 
on t1.video_id =  t2.id
) 
);
"

case $1 in
"dim_user_zip")
    hive -e "$dim_user_zip"
;;
"dim_course_full")
    hive -e "$dim_course_full"
;;
"dim_province_full")
    hive -e "$dim_province_full"
;;
"dim_test_paper_full")
    hive -e "$dim_test_paper_full"
;;
"all")
    hive -e "$dim_user_zip$dim_course_full$dim_province_full$dim_test_paper_full"
;;
esac

